
package com.compuvest.amazon

import groovy.sql.Sql
import javax.naming.InitialContext

class AmazonSQLService {

    def sql

    def AmazonSQLService() {

        def ds = new InitialContext().lookup("jdbc/mysql")
        sql = new Sql(ds)

        sql.eachRow('select VERSION()') { row ->
            println '>>>>AmazonSQLService() version=' << row[0]
        }
        sql.close()
    }

// <editor-fold defaultstate="collapsed" desc="getAmazonNumbers()">

    def getAmazonNumbers() {

        def amazonNo = []
        sql.eachRow("SELECT amazonNo FROM sklad.orders WHERE import='A'")
        { row ->
            amazonNo << row.amazonNo
        }
        sql.close()

        return amazonNo
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="setAmazonNumbersInActive()">
    def static final SET_IMPORT = """UPDATE LOW_PRIORITY sklad.orders SET import='W' WHERE amazonNo=:amazono"""

    def setAmazonNumbersInActive(amazonNoList) {

        sql.withBatch(amazonNoList.size(), SET_IMPORT)
        { stmt ->
            amazonNoList.each { amazonNo ->
                stmt.addBatch(amazono: amazonNo)
            }
        }
        sql.close()
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="fillCustomer()">

    def fillCustomer(cust) {

        def order = ["status": "OK", "body": ""]

        def custId = 0, orderNo = '', amazonNo = '', imprt = ''
        try {
            sql.eachRow("SELECT customer_id,orderNo,amazonNo,import FROM sklad.orders WHERE amazonNo=?", [cust.amazonNo])
            { row ->
                custId = row.customer_id
                orderNo = row.orderNo
                amazonNo = row.amazonNo
                imprt = row.import
            }
            sql.close()

            if(custId == 0) {
                return ["status": "ERROR", "body": "Not find such customer"]
            }
            if(orderNo == '') {
                return ["status": "ERROR", "body": "Not find such order"]
            }
            if(imprt == 'Y') {
                return ["status": "DONE", "body": "Order already imported"]
            }
            order["customerId"] = custId
            order["orderNo"] = orderNo
            order["amazonNo"] = amazonNo

            def checkCustId = 0
            sql.eachRow("SELECT customer_id FROM sklad.customer WHERE email=?", [cust.email])
            { row ->
                checkCustId = row.customer_id
            }
            sql.close()

            if(checkCustId == 0) { // fill shipping new customer
                sql.executeUpdate(
                    "UPDATE LOW_PRIORITY sklad.customer SET email=:email," +
                    "shippingFirstName=:fn,shippingLastName=:ln,shipping_address1=:ad1,shipping_address2=:ad2,shipping_city=:ct," +
                    "shipping_state=:st,shipping_country=:cn,shipping_zip=:zp,shipping_zip4=:zp4,shippingPhone=:ph," +
                    "billingFirstName=:fn,billingLastName=:ln,billing_address1=:ad1,billing_address2=:ad2,billing_city=:ct," +
                    "billing_state=:st,billing_country=:cn,billing_zip=:zp,billing_zip4=:zp4,billingPhone=:ph " +
                    "WHERE customer_id=:custid",
                    ["email": cust.email,
                    "fn":cust.firstname,"ln":cust.lastname,"ad1":cust.address1,"ad2":cust.address2,"ct":cust.city,
                    "st":cust.state,"cn":cust.country,"zp":cust.zip,"zp4":cust.zip4,"ph":cust.phone,
                    "custid": custId])
                sql.close()
            }
            else { // fill shipping old customer
                sql.executeUpdate(
                    "UPDATE LOW_PRIORITY sklad.customer SET " +
                    "count_order=count_order+1,last_order_no=:lstNo," +
                    "shippingFirstName=:fn,shippingLastName=:ln,shipping_address1=:ad1,shipping_address2=:ad2,shipping_city=:ct," +
                    "shipping_state=:st,shipping_country=:cn,shipping_zip=:zp,shipping_zip4=:zp4,shippingPhone=:ph," +
                    "billingFirstName=:fn,billingLastName=:ln,billing_address1=:ad1,billing_address2=:ad2,billing_city=:ct," +
                    "billing_state=:st,billing_country=:cn,billing_zip=:zp,billing_zip4=:zp4,billingPhone=:ph " +
                    "WHERE customer_id=:custid",
                    ["lstNo": orderNo,
                    "fn":cust.firstname,"ln":cust.lastname,"ad1":cust.address1,"ad2":cust.address2,"ct":cust.city,
                    "st":cust.state,"cn":cust.country,"zp":cust.zip,"zp4":cust.zip4,"ph":cust.phone,
                    "custid": checkCustId])
                sql.close()

                sql.executeUpdate("UPDATE LOW_PRIORITY sklad.orders SET customer_id=:custid WHERE amazonNo=:amzNo", ["custid": checkCustId, "amzNo": cust.amazonNo])
                sql.close()
            }
        }
        catch(error) {
            return ["status": "ERROR", "body": "fillCustomer(): ${error}"]
        }

        return order
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="setImport()">

    def setImport(orderNo) {

        def status = ["status": "OK", "body": ""]

        try {
            sql.executeUpdate("UPDATE LOW_PRIORITY sklad.orders SET import='N' WHERE orderNo=?",[orderNo])
            sql.close()
        }
        catch(error) {
            return ["status": "ERROR", "body": "setImport: ${error}"]
        }

        return status
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="cancelOrder()">

    def cancelOrder(amazonNo) {

        def status = ["status": "ERROR", "body": "cancelOrder: not find such amazon order"]

        try {
            def orderNo
            sql.eachRow("SELECT last_order_no FROM sklad.customer WHERE email=?", [amazonNo])
            { row ->
                orderNo = row.last_order_no
            }
            sql.close()

            sql.executeUpdate("UPDATE LOW_PRIORITY sklad.orders SET import='Y' WHERE orderNo=?",[orderNo])
            sql.close()

            status = ["status": "OK", "body": "", "amazonNo": amazonNo, "compuvestNo": orderNo]
        }
        catch(error) {
            return ["status": "ERROR", "body": "cancelOrder: ${error}"]
        }

        return status
    }
// </editor-fold>

// <editor-fold defaultstate="collapsed" desc="getBasket()">
/*
SELECT rd.shipping_price shipPrice,rd.shipping_code shipCode,rd.amount amount,rd.tax_price tax,ip.code couponCode,ip.procent couponProcent,ip.price couponPrice
FROM sklad.orders rd LEFT JOIN sklad.itemsPromotionCode ip ON rd.promoCodeID=ip.id WHERE rd.orderNo=AZ201205300882\G
 **/
    def getBasket(orderNo) {

        def basket = ["status": "OK", "body": ""]

        def orderId = 0
        try {
            sql.eachRow(
                "SELECT rd.order_id orderId,rd.shipping_price shipPrice,rd.shipping_code shipCode,rd.amount amount,rd.tax_price tax," +
                "ip.code couponCode,ip.procent couponProcent,ip.price couponPrice " +
                "FROM sklad.orders rd " +
                "LEFT JOIN sklad.itemsPromotionCode ip ON rd.promoCodeID=ip.id " +
                "WHERE rd.orderNo=?",
                [orderNo]
            ) { row ->
                orderId = row.orderId
                basket = [
                    "status"        : "OK",
                    "body"          : "",
                    "orderid"       : orderId,
                    "ship_price"    : row.shipPrice,
                    "ship_code"     : row.shipCode,
                    "amount"        : row.amount,
                    "tax"           : row.tax,
                    "couponCode"    : row.couponCode,
                    "couponProcent" : row.couponProcent,
                    "couponPrice"   : row.couponPrice
                ]
            }
            sql.close()

            def items = [:]
            sql.eachRow(
                "SELECT sit.accwareCatID accwareID,sol.quantity qty,sol.sell_price sprice,sit.name name,sol.warrantyPrice wprice,sol.warrantyDesc wdesc " +
                "FROM sklad.order_line sol " +
                "LEFT JOIN sklad.items sit USING(item_id) " +
                "WHERE order_id=?",
                [orderId]
            ) { row ->
                items[row.accwareID] = [
                        "accwareID"     : row.accwareID,
                        "qty"           : row.qty,
                        "sellprice"     : row.sprice,
                        "name"          : row.name,
                        "warnprice"     : row.wprice,
                        "warndesc"      : row.wdesc
                    ]
            }
            sql.close()

            basket["items"] = items
        }
        catch(error) {
            return ["status": "ERROR", "body": "getBasket: : ${error}"]
        }

        return basket
    }
// </editor-fold>

}

